import pool from "@/lib/db";

const userModule = {
  get: async (username) => {
    const sql = ` SELECT u.*, r.id as role_id, r.name as role_name, r.code as role_code, r.description as role_description, r.created_at as role_created_at, r.updated_at as role_updated_at FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.username = ? LIMIT 1 `;
    const [rows] = await pool.query(sql, [username]);
    if (!rows || rows.length === 0) return null;
    const user = rows[0];
    return { id: user.id, username: user.username, password: user.password, nickname: user.nickname, avatar: user.avatar, mobile: user.mobile, email: user.email, status: user.status, created_at: user.created_at, updated_at: user.updated_at, role_id: user.role_id, roleInfo: { id: user.role_id, name: user.role_name, code: user.role_code, description: user.role_description, created_at: user.role_created_at, updated_at: user.role_updated_at } };
  },
  getById: async (id) => {
    const sql = ` SELECT u.*, r.id as role_id, r.name as role_name, r.code as role_code, r.description as role_description, r.created_at as role_created_at, r.updated_at as role_updated_at FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.id = ? LIMIT 1 `;
    const [rows] = await pool.query(sql, [id]);
    if (!rows || rows.length === 0) return null;
    const user = rows[0];
    return { id: user.id, username: user.username, password: user.password, nickname: user.nickname, avatar: user.avatar, mobile: user.mobile, email: user.email, status: user.status, created_at: user.created_at, updated_at: user.updated_at, role_id: user.role_id, roleInfo: { id: user.role_id, name: user.role_name, code: user.role_code, description: user.role_description, created_at: user.role_created_at, updated_at: user.role_updated_at } };
  },
  getDetailed: async (username) => {
    const sql = ` SELECT u.*, r.id as role_id, r.name as role_name, r.code as role_code, r.description as role_description, r.created_at as role_created_at, r.updated_at as role_updated_at FROM users u LEFT JOIN roles r ON u.role_id = r.id WHERE u.username = ? LIMIT 1 `;
    const [rows] = await pool.query(sql, [username]);
    if (!rows || rows.length === 0) return null;
    const user = rows[0];
    if (user.role_code === 0) {
      const [allPermRows] = await pool.query(`SELECT id FROM permissions`);
      const allPermIds = allPermRows.map(p => p.id);
      const [rolePermRows] = await pool.query(`SELECT permission_id FROM role_permission WHERE role_id = ?`, [user.role_id]);
      const rolePermIds = rolePermRows.map(rp => rp.permission_id);
      const missingPermIds = allPermIds.filter(id => !rolePermIds.includes(id));
      if (missingPermIds.length > 0) {
        const values = missingPermIds.map(pid => `(${user.role_id},${pid})`).join(",");
        await pool.query(`INSERT IGNORE INTO role_permission (role_id, permission_id) VALUES ${values}`);
      }
    }
    let permissions = [];
    if (user.role_id) {
      const permSql = ` SELECT p.id, p.name, p.path, p.icon, p.sort, p.parent_id, p.created_at, p.updated_at FROM permissions p INNER JOIN role_permission rp ON p.id = rp.permission_id WHERE rp.role_id = ? ORDER BY p.sort ASC, p.id ASC `;
      const [permRows] = await pool.query(permSql, [user.role_id]);
      permissions = permRows.map(p => ({ id: p.id, name: p.name, path: p.path, icon:p.icon, sort: p.sort, parent_id: p.parent_id, created_at: p.created_at, updated_at: p.updated_at, }));
    }
    return { id: user.id, username: user.username, password: user.password, nickname: user.nickname, avatar: user.avatar, mobile: user.mobile, email: user.email, status: user.status, created_at: user.created_at, updated_at: user.updated_at, role_id: user.role_id, roleInfo: { id: user.role_id, name: user.role_name, code: user.role_code, description: user.role_description, created_at: user.role_created_at, updated_at: user.role_updated_at, permissions, } };
  },
  getAllDetailed: async () => {
    const sql = ` SELECT u.*, r.id as role_id, r.name as role_name, r.code as role_code, r.description as role_description, r.created_at as role_created_at, r.updated_at as role_updated_at FROM users u LEFT JOIN roles r ON u.role_id = r.id `;
    const [rows] = await pool.query(sql);
    if (!rows || rows.length === 0) return [];
    const [rolePermRows] = await pool.query(` SELECT rp.role_id, p.id, p.name, p.path, p.icon, p.sort, p.parent_id, p.created_at, p.updated_at FROM permissions p INNER JOIN role_permission rp ON p.id = rp.permission_id ORDER BY p.sort ASC, p.id ASC `);
    const rolePermMap = {};
    for (const row of rolePermRows) {
      if (!rolePermMap[row.role_id]) rolePermMap[row.role_id] = [];
      rolePermMap[row.role_id].push({ id: row.id, name: row.name, path: row.path, icon:row.icon, sort: row.sort, parent_id: row.parent_id, created_at: row.created_at, updated_at: row.updated_at, });
    }
    return rows.map(user => ({ id: user.id, username: user.username, nickname: user.nickname, avatar: user.avatar, mobile: user.mobile, email: user.email, status: user.status, created_at: user.created_at, updated_at: user.updated_at, role_id: user.role_id, roleInfo: { id: user.role_id, name: user.role_name, code: user.role_code, description: user.role_description, created_at: user.role_created_at, updated_at: user.role_updated_at, permissions: rolePermMap[user.role_id] || [], } }));
  },
  getUsersCountByRoles: async (roleIds) => {
    const ids = Array.isArray(roleIds) ? roleIds : [roleIds];
    const placeholders = ids.map(() => '?').join(',');
    const sql = `SELECT role_id, COUNT(*) AS user_count FROM users WHERE role_id IN (${placeholders}) GROUP BY role_id`;
    const [result] = await pool.query(sql, ids);
    if (!result || result.length === 0) return 0;
    return result.reduce((sum, item) => sum + item.user_count, 0);
  },
  post: async (username, password, status, nickname, avatar, mobile, email, role_id) => {
    const sql = 'INSERT INTO users (username, password, status, nickname, avatar, mobile, email, role_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?)';
    const [result] = await pool.query(sql, [username, password, status, nickname || '未设置昵称', avatar || '', mobile || '', email || '', role_id]);
    return result;
  },
  update: async (id, username, password, status, nickname, avatar, mobile, email, role_id) => {
    const sql = 'UPDATE users SET username = ?, password = ?, status = ?, nickname = ?, avatar = ? , mobile = ?, email = ?, role_id = ? WHERE id = ?';
    const [result] = await pool.query(sql, [username, password, status, nickname, avatar, mobile, email, role_id, id]);
    return result;
  },
  count: async () => {
    const sql = 'SELECT COUNT(*) as count FROM users';
    const [rows] = await pool.query(sql);
    return rows;
  },
  deleteById: async (ids) => {
    const sql = 'DELETE FROM users WHERE id IN (?)';
    ids = Array.isArray(ids) ? ids : [ids];
    const [result] = await pool.query(sql, ids);
    return result;
  }
}

export default userModule